const Router = require('koa-router');
const router = new Router();
const tool = require('../../tools/tool')
const {db, QueryTypes} = require("../../models/db");


// 其他收支明细
router.post('/report/qtszmx', async  ctx => {
    let user = ctx.user
    let body = ctx.request.body
    let tenantId = user.tenantId;
    let excel = body.excel ? 1 : 0;
    let search = body.formData;
    let beginDate = search.beginDate ? search.beginDate : '';
    let endDate = search.endDate ? search.endDate : '';
    if(!beginDate) return ctx.err('请选择开始日期')
    if(!endDate) return ctx.err('请选择结束日期')
    let remark = search.remark ? search.remark : '';
    let buId = search.buId ? search.buId : 0;
    let sqls =  `select a.id,a.billDate,a.billNo,a.transTypeName,a.transType,b.subjectName,
      case when a.billType='SR' then b.amount else 0 end sr,
      case when a.billType='ZC' then b.amount else 0 end zc,
      c.name buName,a.remark 
      from invoice a inner join invoice_other b on a.tenantId=b.tenantId and a.id=b.iid and a.tenantId=${tenantId} 
   and a.billState ='accounted' and (a.buId=${buId} or ${buId} = 0) and a.billDate between '${beginDate}' and '${endDate}' and a.billType in ('SR','ZC')
left join contact c on a.tenantId=c.tenantId and a.buId=c.id 
   `
    if(remark) {
        sqls += ` and a.remark like '%${remark}%' `
    }

    if (excel) {
        sqls += " order by a.buId,a.billDate,a.transType ";
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return tool.doExcelOut(ctx, list)
    } else {
        sqls += " order by a.buId,a.billDate,a.transType ";
        let list = await db.query(sqls, {type: QueryTypes.SELECT})
        return ctx.suc('', {
            total: 0,
            list
        });
    }
})



module.exports = router;
